package com.cz.mes;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import com.cz.dto.ProductionMaterialDTO;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;

public class ProductionMaterialExport {

    public static void main(String[] args) {
        try {
            Field[] fields = ReflectUtil.getFields(ProductionMaterialDTO.class);

            Map<String, String> businessStateMap = new HashMap<>();
            businessStateMap.put("0", "计划");
            businessStateMap.put("1", "计划确认");
            businessStateMap.put("2", "工艺认领");
            businessStateMap.put("3", "下达");
            businessStateMap.put("4", "开工");
            businessStateMap.put("5", "完工");
            businessStateMap.put("6", "结案");
            businessStateMap.put("7", "强制结案");
            businessStateMap.put("8", "驳回");


            List<ProductionMaterialDTO> productionMaterialDTOList = new ArrayList<>();

            String sql ="select id,production_material_id,create_on,item_unit,item_sku_code,item_sku_name from production_material_detail where status = 1 and create_on >= '2025-04-01 00:00:00' and create_on <= '2025-05-01 00:00:00'";
            List<Entity> entityList = Db.use("mes-prod-slave").query(sql);
            System.out.println("数据 条数："+entityList.size());
            int count = 0;
            for(Entity entity : entityList){
                ProductionMaterialDTO productionMaterialDTO = new ProductionMaterialDTO();

                String create_on = entity.getStr("create_on");
                String item_unit = entity.getStr("item_unit");
                String item_sku_code  = entity.getStr("item_sku_code");
                String item_sku_name  = entity.getStr("item_sku_name");
                Integer production_material_id = entity.getInt("production_material_id");
                Integer production_material_detail_id = entity.getInt("id");

                productionMaterialDTO.setCreateTime(create_on);
                productionMaterialDTO.setSubItemMaterialUnit(item_unit);
                productionMaterialDTO.setSubItemMaterialCode(item_sku_code);
                productionMaterialDTO.setSubItemMaterialName(item_sku_name);

                String sql1 = "select bill_no,moc_no,moc_detail_id,sku_name,sku_code,quantity,customer_name,unit from production_material where status =1 and id = "+production_material_id;
                Entity productionMaterial =  Db.use("mes-prod-slave").queryOne(sql1);
                String billNo = productionMaterial.getStr("bill_no");
                String moc_no = productionMaterial.getStr("moc_no");
                String moc_detail_id = productionMaterial.getStr("moc_detail_id");
                String sku_name = productionMaterial.getStr("sku_name");
                String sku_code = productionMaterial.getStr("sku_code");
                String quantity = productionMaterial.getStr("quantity");
                String unit = productionMaterial.getStr("unit");
                String customer_name = productionMaterial.getStr("customer_name");

                productionMaterialDTO.setProductionMaterialNo(billNo);
                productionMaterialDTO.setProductionOrderNo(moc_no);
                productionMaterialDTO.setProductName(sku_name);
                productionMaterialDTO.setProductCode(sku_code);
                productionMaterialDTO.setProductQuantity(quantity);
                productionMaterialDTO.setProductUnit(unit);
                productionMaterialDTO.setCustomer(customer_name);


                String sql2 = "select business_state,sale_bill_no,sale_bill_no from manufacture_order_detail where status = 1  and  id = "+ moc_detail_id;
                Entity manufactureOrderDetail = Db.use("mes-prod-slave").queryOne(sql2);

                if(manufactureOrderDetail == null){
                    System.out.println("没有找到订单明细 moc_no:"+moc_no);
                }else {
                    String business_state = manufactureOrderDetail.getStr("business_state");
                    if(StrUtil.isNotBlank(business_state)){
                        String business_state_dict = businessStateMap.get(business_state);
                        if(StrUtil.isNotBlank(business_state_dict)){
                            productionMaterialDTO.setProductionOrderStatus(business_state_dict);
                        }
                    }

                    String sale_bill_no = manufactureOrderDetail.getStr("sale_bill_no");
                    productionMaterialDTO.setSalesOrderNo(sale_bill_no);
                }


                String sql3 = "select sum(actual_quantity) as total_actual_quantity from production_requisition where status = 1 and bill_state =1 and production_material_detail_id = "+production_material_detail_id;
                Number total_actual_quantity = Db.use("mes-prod-slave").queryNumber(sql3);
                if(Objects.nonNull(total_actual_quantity)){
                    productionMaterialDTO.setReceivedQuantity(total_actual_quantity.toString());
                }

                productionMaterialDTOList.add(productionMaterialDTO);
                if (count % 1000 == 0) {
                    System.out.println("已处理数据条数：" + count);
                }
                count++;
            }
            if(CollUtil.isNotEmpty(productionMaterialDTOList)){
                if(FileUtil.exist("D:\\生产物料明细.xlsx")){
                    FileUtil.del("D:\\生产物料明细.xlsx");
                }
                BigExcelWriter writer= ExcelUtil.getBigWriter("D:\\生产物料明细.xlsx");

                for(int i = 0;i<fields.length;i++){
                    writer.setColumnWidth(i, 20); // 姓名列
                }
                writer.write(productionMaterialDTOList,true);
                writer.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
